package database;

import interfaces.SQLiteLoader;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;

import com.google.gson.Gson;

import data.Athlete;
import data.Competition;
import data.Event;
import data.Relay;
import data.Result;
import data.Team;

public class HY_TEK_databaseController extends DataController {

	public HY_TEK_databaseController(Connection connection) {
		super(connection);
	}
	
	@Override
	public String loadCompetition() throws SQLException {
		String json = "";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT Meet_name1, Meet_location, Meet_start, Meet_end, Meet_course FROM Meet");
		while(rs.next())
		{
			Competition competition = new Competition(
					rs.getString("Meet_name1"), 
					rs.getString("Meet_location"), 
					new DateTime(rs.getLong("Meet_start")).toString("yyyy-MM-dd"),  
					new DateTime(rs.getLong("Meet_end")).toString("yyyy-MM-dd"),
					rs.getInt("Meet_course") == 1 ? 50 : 25
					);
			json = new Gson().toJson(competition);
		}
		return json;
	}

	@Override
	public String loadTeams() throws SQLException {
		List<Team> teams = new ArrayList<Team>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT Team_no, Team_name, Team_short, Team_abbr FROM Team");
		while(rs.next())
		{
			teams.add(new Team(
					rs.getInt("team_no"),
					rs.getString("Team_name").trim(), 
					rs.getString("Team_short").trim(), 
					rs.getString("Team_abbr").trim()
					));
		}

		return new Gson().toJson(teams);
	}

	@Override
	public String loadAthletes() throws SQLException {
		List<Athlete> athletes = new ArrayList<Athlete>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT Ath_no as ath_no, Team.Team_name as team, Last_name, First_name, Birth_date, Ath_Sex FROM Athlete, Team WHERE Athlete.Team_no = Team.Team_no");
		while(rs.next())
		{
			athletes.add(new Athlete(
					rs.getInt("ath_no"),
					rs.getString("First_name").trim(), 
					rs.getString("Last_name").trim(), 
					rs.getString("team").trim(), 
					new DateTime(rs.getLong("Birth_date")).toString("yyyy-MM-dd"),
					rs.getString("Ath_Sex").trim()
					));
		}
		return new Gson().toJson(athletes);
	}

	@Override
	public List<Event> loadEvents() throws SQLException {
		List<Event> events = new ArrayList<Event>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT COUNT(*) as multisession FROM Sessitem");

		if (rs.getInt("multisession") == 0) {
			rs = statement.executeQuery("SELECT Event.Event_ptr as event_id, Event_no as nr, Event_dist as distance, CASE Event_stroke WHEN 'A' then 'free' WHEN 'B' THEN 'back' WHEN 'C' THEN 'breast' WHEN 'D' THEN 'fly' WHEN 'E' THEN 'medley' END as stroke, CASE Ind_rel WHEN 'R' THEN 1 WHEN 'I' THEN 0 END as relay, Low_age as low_age, High_Age as high_age, Event_gender as gender, CASE Event_Type WHEN ' ' THEN 'F' END as type, Meet_start as date FROM Event, Meet GROUP BY Event_no, type ORDER BY event_id");
		} else {
			rs = statement.executeQuery("SELECT Event.Event_ptr as event_id, Event_no as nr, Event_dist as distance, CASE Event_stroke WHEN 'A' then 'free' WHEN 'B' THEN 'back' WHEN 'C' THEN 'breast' WHEN 'D' THEN 'fly' WHEN 'E' THEN 'medley' END as stroke, CASE Ind_rel WHEN 'R' THEN 1 WHEN 'I' THEN 0 END as relay, Low_age as low_age, High_Age as high_age, Event_gender as gender, CASE Event_Type WHEN 'S' THEN 'SO' ELSE Sess_rnd END as type, (Sess_day -1) * 86400000 + Meet_start as date FROM Event, Sessitem, Session, Meet WHERE Sessitem.Sess_ptr = Session.Sess_ptr AND Event.Event_ptr = Sessitem.Event_ptr GROUP BY Event_no, type ORDER BY event_id");
		}
		while(rs.next())
		{
			events.add(new Event(
					rs.getInt("event_id"),
					rs.getInt("nr"),
					rs.getInt("distance"),
					rs.getString("stroke").trim(), 
					rs.getInt("relay"),
					rs.getInt("low_age"),
					rs.getInt("high_age"),
					rs.getString("gender").trim(), 
					rs.getString("type").trim(), 
					new DateTime(rs.getLong("date")).toString("yyyy-MM-dd")
					));
		}

		return events;
	}

	@Override
	public List<Result> loadResults() throws SQLException {
		List<Result> results = new ArrayList<Result>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT Entry.Event_ptr as event_id, CASE WHEN Event_Type = 'S' THEN 'SO' ELSE 'P' END as type, Entry.Ath_no as ath_no, Team.Team_no as team_no, CASE Pre_exh WHEN 'X' THEN 1 ELSE 0 END as exhibition, ConvSeed_time as seed, CASE WHEN Pre_stat = 'Q' THEN 'DQ' ELSE Pre_Time END as result FROM Team, Athlete, Entry, Event WHERE Event.Event_ptr = Entry.Event_ptr AND Athlete.Team_no = Team.Team_no AND Athlete.Ath_no = Entry.Ath_no AND Pre_time >= 0 AND NOT Pre_stat = 'R' AND Scr_stat = 0 UNION ALL SELECT Entry.Event_ptr as event_id,  'F' as type, Entry.Ath_no as ath_no, Team.Team_no as team_no,CASE Fin_exh WHEN 'X' THEN 1 ELSE 0 END as exhibition, CASE WHEN Pre_time >= 0 THEN Pre_time ELSE ConvSeed_time END as seed,CASE WHEN Fin_stat = 'Q' THEN 'DQ' ELSE Fin_Time END as result  FROM Team, Athlete, Entry WHERE  Athlete.Team_no = Team.Team_no AND Athlete.Ath_no = Entry.Ath_no AND Fin_Time> 0 AND NOT Fin_stat = 'R' AND Scr_stat = 0 ORDER BY event_id, Entry.Ath_no");
		while(rs.next())
		{
			Result r = new Result(
					rs.getInt("event_id"),
					rs.getString("type"),
					rs.getInt("ath_no"),
					rs.getInt("team_no"),
					(double) Math.round(rs.getDouble("seed") * 100) / 100,
					rs.getString("result").equals("DQ") ? rs.getString("result") : 
						String.valueOf((double) Math.round(Double.parseDouble(rs.getString("result")) * 100) / 100),
						rs.getInt("exhibition")
					);
			r.setSplits(loadSplits(r.getAth_no(), r.getEvent_id(), r.getEvent_type(), false));
			results.add(r);

		}

		return results;
	}

	public List<Double> loadSplits(Integer no, Integer event_id, String event_type, boolean relay) throws SQLException {
		
		String column = relay ? "Relay_no" : "Ath_no";
		
		List<Double> splits = new ArrayList<Double>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(String.format("SELECT Split_Time as time FROM Split WHERE %s = %d AND Event_ptr = %d AND Rnd_ltr = '%s' ORDER BY Split_no", column, no, event_id, event_type));
		while(rs.next())
		{
			splits.add((double) Math.round(rs.getDouble("time") * 100) / 100);
		}
		return splits;
	}

	@Override
	public List<Relay> loadRelays() throws SQLException {
		List<Relay> relays = new ArrayList<Relay>();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT Relay.Event_ptr as event_id, Relay.Relay_no as relay_no, GROUP_CONCAT(Pos_no || '=' || RelayNames.Ath_no) as athlete_list, Relay.Team_no as team_no, Relay.Team_ltr as relay_team, Event_round as event_type, CASE Fin_exh WHEN 'X' THEN 1 ELSE 0 END as exhibition,  ConvSeed_time as seed, CASE WHEN Fin_stat = 'Q' THEN 'DQ' ELSE Fin_Time END as result   FROM Team, Relay, RelayNames WHERE RelayNames.Team_no = Team.Team_no AND Relay.Relay_no = RelayNames.Relay_no AND Event_round = 'F' AND NOT Fin_stat = 'R' GROUP BY event_id, Team.team_no, RelayNames.Relay_no ORDER BY event_id, Team.team_no");
		while(rs.next())
		{
			Relay r = new Relay(rs.getInt("event_id"), 
					rs.getString("event_type"), 
					rs.getInt("team_no"), 
					rs.getString("relay_team"), 
					rs.getString("athlete_list"),
					(double) Math.round(rs.getDouble("seed") * 100) / 100,
					rs.getString("result").equals("DQ") ? rs.getString("result") : 
						String.valueOf((double) Math.round(Double.parseDouble(rs.getString("result")) * 100) / 100),
						rs.getInt("exhibition"),
						rs.getInt("relay_no")
					);

			String [] map = r.getAthlete_list().split(",");
			for (String s : map) {
				String [] splits = s.split("=");
				int nr = Integer.parseInt(splits[0]);
				int ath_no = Integer.parseInt(splits[1]);
				switch (nr) {
				case 1:
					r.setAth_no1(ath_no);
					break;
				case 2:
					r.setAth_no2(ath_no);
					break;
				case 3:
					r.setAth_no3(ath_no);
					break;
				case 4:
					r.setAth_no4(ath_no);
					break;

				default:
					break;
				}
			}
			r.setAthlete_list(null);
			r.setSplits(loadSplits(r.getRelay_no(), r.getEvent_id(), r.getEvent_type(), true));
			relays.add(r);
		}

		return relays;
	}

}
